package dao.Seller.impl;

import dao.BaseDao;
import dao.Seller.GoodsDao;
import pojo.Goods;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoodsImpl extends BaseDao implements GoodsDao {

    /**
     * 查询所有的商品
     * @return
     */
    @Override
    public List<Goods> getGoodes() {
        List<Goods> Listgoods =new ArrayList<>();
        Connection connection=null;
        Statement statement=null;
        ResultSet rs=null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            String url="jdbc:mysql://localhost:3306/GuiMei";
            connection= DriverManager.getConnection(url,"root","root");
            //创建statument执行sql
            statement=connection.createStatement();
            String sql=" SELECT * FROM goods";
            rs=statement.executeQuery(sql);
            Goods goods=null;
            //返回结果
            while(rs.next()){
                goods=new Goods();
                int id =rs.getInt("id");
                String goodsName=rs.getString("goodsName");
                long goodsSmalId=rs.getLong("goodsSmalId");
                double goodsMoney=rs.getDouble("goodsMoney");
                long goodsNumber=rs.getLong("goodsNumber");
                String goodsImage=rs.getString("goodsImage");
                double goodsCarriage=rs.getDouble("goodsCarriage");
                long goodsType =rs.getLong("goodsType");
                long goodsSeId=rs.getLong("goodsSeId");
                long goodsDiscId=rs.getLong("goodsDiscId");
                goods.setId(id);
                goods.setGoodsName(goodsName);
                goods.setGoodsSmalId(goodsSmalId);
                goods.setGoodsCarriage(goodsCarriage);
                goods.setGoodsDiscId(goodsDiscId);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsMoney(goodsMoney);
                goods.setGoodsNumber(goodsNumber);
                goods.setGoodsType(goodsType);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsImage(goodsImage);
                Listgoods.add(goods);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            try {
                if(rs!=null) {
                    rs.close();
                }
                if(statement!=null){
                    statement.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return Listgoods;
    }

    /**
     * 添加商品信息
     * @param goods
     * @return
     */
    @Override
    public int addGoods(Goods goods) {
        int ire =0;
        try {
           String sql="INSERT INTO goods (goodsName,goodsSmalId,goodsMoney,goodsNumber,goodsImage,goodsCarriage,goodsType,goodsDiscId) VALUES(?,?,?,?,?,?,?,?)";
           Object[] params={goods.getGoodsName(),goods.getGoodsSmalId(),goods.getGoodsMoney(),goods.getGoodsNumber(),goods.getGoodsImage(),goods.getGoodsCarriage(),goods.getGoodsType(),goods.getGoodsDiscId()};
           ire=this.executeUpdateRows(sql,params);
           if(ire>0){
               System.out.println("数据添加成功！");
           }
        }catch (Exception EX){
            EX.printStackTrace();
        }finally {
            closeAll();
        }
        return ire;
    }

    /**
     * 删除商品信息
     * @param id
     * @return
     */
    @Override
    public int deleteGoods(int id) {
        int ire =0;
        try{
            String sql="delete from goods where id=?";
            Object[] params={id};
            ire=this.executeUpdateRows(sql,params);
            if(ire>0){
                System.out.println("删除成功！");
            }
        }catch (Exception EX){
            EX.printStackTrace();
        }finally {
            closeAll();
        }
        return ire;
    }

    /**
     * 修改商品信息
     * @param goods
     * @return
     */
    @Override
    public int updataGoods(Goods goods) {
        int ire =0;
        try{
            String sql="update goods set goodsName=?,goodsSmalId=?,goodsMoney=?,goodsNumber=?,goodsImage=?,goodsCarriage=?,goodsType=?,goodsSeId=?,goodsDiscId=? where id=?";
            Object[] params={goods.getGoodsName(),goods.getGoodsSmalId(),goods.getGoodsMoney(),goods.getGoodsNumber(),goods.getGoodsImage(),goods.getGoodsCarriage(),goods.getGoodsType(),goods.getGoodsSeId(),goods.getGoodsDiscId(),goods.getId()};
            ire=this.executeUpdateRows(sql,params);
            if(ire>0){
                System.out.println("数据更新成功！");
            }
        }catch (Exception EX){
            EX.printStackTrace();
        }finally {
            closeAll();
        }
        return ire;
    }

    /**
     * 根据商品名称模糊查询
     * @param goodsNames
     * @return
     */
    @Override
    public List<Goods> getFuzzyquery(String goodsNames,int sellerid,int smallId) {
        List<Goods> Listgoods =new ArrayList<>();
        try{
            StringBuffer sb= new StringBuffer("SELECT DISTINCT goods.*, seller.`sellerName`,seller.`id`,smallclass.`smallName`,smallclass.`id`\n" +
                    "    FROM `goods`\n" +
                    "    INNER JOIN `seller` ON  `goods`.`goodsSeId`=`seller`.`id`\n" +
                    "    INNER JOIN `smallclass` ON `goods`.`goodsSmalId`=`smallclass`.`id`  WHERE 1=1");
            if(goodsNames!=null && goodsNames!=""){
                sb.append(" and  goods.goodsName like " + "\'%" + goodsNames + "%\'");
            }
            if(sellerid!=-1){
                sb.append(" AND goods.`goodsSeId` ="+sellerid);
            }
            if(smallId!=-1){
                sb.append("  AND goods.`goodsSmalId` ="+smallId);
            }
            Object[] params={};
            ResultSet rs= this.executeSelect(sb.toString(),params);
            while(rs.next()) {
                Goods goods = new Goods();
                int id = rs.getInt("id");
                String goodsName = rs.getString("goodsName");
                long goodsSmalId = rs.getLong("goodsSmalId");
                double goodsMoney = rs.getDouble("goodsMoney");
                long goodsNumber = rs.getLong("goodsNumber");
                String goodsImage = rs.getString("goodsImage");
                double goodsCarriage = rs.getDouble("goodsCarriage");
                long goodsType = rs.getLong("goodsType");
                long goodsSeId = rs.getLong("goodsSeId");
                long goodsDiscId = rs.getLong("goodsDiscId");
                goods.setId(id);
                goods.setGoodsName(goodsName);
                goods.setGoodsSmalId(goodsSmalId);
                goods.setGoodsCarriage(goodsCarriage);
                goods.setGoodsDiscId(goodsDiscId);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsMoney(goodsMoney);
                goods.setGoodsNumber(goodsNumber);
                goods.setGoodsType(goodsType);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsImage(goodsImage);
                Listgoods.add(goods);
            }
        }catch (Exception EX){
            EX.printStackTrace();
        }
        return Listgoods;
    }

    /**
     *获得商品的id
     * @param Goodesid
     * @return
     */
    @Override
    public Goods getGoodsId(int Goodesid) {
        Goods goods= new Goods();
        try{
            String sql="SELECT  DISTINCT goods.* FROM `goods` \n" +
                    "INNER JOIN `seller` ON `goods`.`goodsSeId`=`seller`.`id`\n" +
                    "INNER JOIN `smallclass` ON `goods`.`goodsSmalId`=`smallclass`.`id`\n" +
                    "INNER JOIN `discount` ON goods.`goodsDiscId`=`discount`.`id`\n" +
                    "WHERE goods.`id`=?";
            Object[] params={Goodesid};
            ResultSet rs=this.executeSelect(sql,params);
            while (rs.next()){
                int id =rs.getInt("id");
                String goodsName=rs.getString("goodsName");
                long goodsSmalId=rs.getLong("goodsSmalId");
                double goodsMoney=rs.getDouble("goodsMoney");
                long goodsNumber=rs.getLong("goodsNumber");
                String goodsImage=rs.getString("goodsImage");
                double goodsCarriage=rs.getDouble("goodsCarriage");
                long goodsType =rs.getLong("goodsType");
                long goodsSeId=rs.getLong("goodsSeId");
                long goodsDiscId=rs.getLong("goodsDiscId");
                goods.setId(id);
                goods.setGoodsName(goodsName);
                goods.setGoodsSmalId(goodsSmalId);
                goods.setGoodsCarriage(goodsCarriage);
                goods.setGoodsDiscId(goodsDiscId);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsMoney(goodsMoney);
                goods.setGoodsNumber(goodsNumber);
                goods.setGoodsType(goodsType);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsImage(goodsImage);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return goods;
    }

    /**
     * 求得总行数
     * @param cId
     * @param Names
     * @return
     */
    @Override
    public int getCount(int cId, String Names) {
        int count =0;
        StringBuffer sql= new StringBuffer("SELECT count(*) FROM goods where 1=1");
        if(cId!=-1){
            sql.append(" and id="+cId);
        }
        if (Names !=null && Names!=""){
            Names="'%"+Names+"%'";
            sql.append(" and goodsName like"+Names);
        }
        try {
            Object[] params={};
            ResultSet rs=this.executeSelect(sql.toString(),params);
            if(rs.next()){
                count=rs.getInt(1); //从第一条数据开始
            }
        }catch (Exception EX){
            EX.printStackTrace();
        }
        return count;
    }

    /**
     * 求得总页数
     * @param count
     * @param page
     * @return
     */
    @Override
    public int getCountPage(int count, int page) {
        int num =0;
        num = count%page ==0?count/page : count/page+1;
        return num;
    }

    /**
     * 分页显示
     * @param cId
     * @param name
     * @param index
     * @param page
     * @return
     */
    @Override
    public List<Goods> NewsfindAll(int cId, String name, int index, int page) {
        List<Goods> Listgoods =new ArrayList<>();
        try {
            StringBuffer sql=new StringBuffer("SELECT * FROM goods where 1=1");
            if(cId!=-1){
                sql.append(" and id="+cId);
            }
            if(name!=null && name!=""){
                name="'%"+name+"%'";
                sql.append(" and goodsSmalId like"+name);
            }
            sql.append(" limit ?,?");
            Object[] params={(index-1)*page,page};
            ResultSet rs=this.executeSelect(sql.toString(),params);
            while(rs.next()) {
                Goods goods = new Goods();
                int id = rs.getInt("id");
                String goodsName = rs.getString("goodsName");
                long goodsSmalId = rs.getLong("goodsSmalId");
                double goodsMoney = rs.getDouble("goodsMoney");
                long goodsNumber = rs.getLong("goodsNumber");
                String goodsImage = rs.getString("goodsImage");
                double goodsCarriage = rs.getDouble("goodsCarriage");
                long goodsType = rs.getLong("goodsType");
                long goodsSeId = rs.getLong("goodsSeId");
                long goodsDiscId = rs.getLong("goodsDiscId");
                goods.setId(id);
                goods.setGoodsName(goodsName);
                goods.setGoodsSmalId(goodsSmalId);
                goods.setGoodsCarriage(goodsCarriage);
                goods.setGoodsDiscId(goodsDiscId);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsMoney(goodsMoney);
                goods.setGoodsNumber(goodsNumber);
                goods.setGoodsType(goodsType);
                goods.setGoodsSeId(goodsSeId);
                goods.setGoodsImage(goodsImage);
                Listgoods.add(goods);
            }
        }catch (Exception EX){
            EX.printStackTrace();
        }finally {
            closeAll();
        }
        return Listgoods;
    }
}

